/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cz.wors.android.multirideshare.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import cz.wors.android.multirideshare.activity.RideShareActivity;
import cz.wors.android.multirideshare.constants.ViewConst;
import cz.wors.android.multirideshare.model.BooleanMesssage;
import cz.wors.android.multirideshare.model.Driveway;
import cz.wors.android.multirideshare.model.Payment;
import cz.wors.android.multirideshare.model.Setting;
import java.util.ArrayList;

public class DBAdapter 
{
    public static final String KEY_ROWID = "id";   
    private static final String TAG = "DBAdapter";
    
    private static final String DATABASE_NAME = "rideshare";
    private static final int DATABASE_VERSION = 1;

	private static final String DATABASE_DROP_1 = "DROP TABLE IF EXISTS contacts;";
	//private static final String DATABASE_DROP_2 = "DROP TABLE IF EXISTS prizes;";
	private static final String DATABASE_DROP_3 = "DROP TABLE IF EXISTS drivers;";
	private static final String DATABASE_DROP_4 = "DROP TABLE IF EXISTS driveways;";
	private static final String DATABASE_DROP_5 = "DROP TABLE IF EXISTS payments;";
	private static final String DATABASE_DROP_6 = "DROP TABLE IF EXISTS settings;";
	
    private static final String DATABASE_CREATE_1 =
        "create table contacts ("
				+"id integer primary key autoincrement, "
				+ "name text not null"
		+");";
	/* ruseni tabulky - je k nicemu, cena se muze vazat primo k ridici
    private static final String DATABASE_CREATE_2 =
        "create table prizes ("
				+"id integer primary key autoincrement, "
				+"price integer not null"
		+");";
	*/
    private static final String DATABASE_CREATE_3 =
        "create table drivers ("
				+"id integer primary key autoincrement, "
				+"contact_id integer not null, "
				+"price integer not null"
		+");";
    private static final String DATABASE_CREATE_4 =
        "create table driveways ("
				+"id integer primary key autoincrement, "
				+"price integer not null, "
				+"direction boolean not null, "
				+"date text not null, "
				+"driver_id integer not null, "
				+"passenger_id integer not null"
		+");";
	private static final String DATABASE_CREATE_5 =
        "create table payments ("
				+"id integer primary key autoincrement, "
				+"amount integer not null, "
				+"date text not null, "
				+"receiving_id integer not null, "
				+"paying_id integer not null, "
				+"note text"
		+");";
	private static final String DATABASE_CREATE_6 =
        "create table settings ("
				+"id integer primary key autoincrement, "
				+"name text not null, "
				+"value text not null, "
				+"note text"
		+");";
        
    private final Context context; 
    
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DBAdapter(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }
        
    private static class DatabaseHelper extends SQLiteOpenHelper 
    {
        DatabaseHelper(Context context) 
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) 
        {
            db.execSQL(DATABASE_CREATE_1);
			//db.execSQL(DATABASE_CREATE_2);
			db.execSQL(DATABASE_CREATE_3);
			db.execSQL(DATABASE_CREATE_4);
			db.execSQL(DATABASE_CREATE_5);
			db.execSQL(DATABASE_CREATE_6);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, 
        int newVersion) 
        {
            if (ViewConst.DEBUG) {
				Log.i(TAG, "Upgrading database from version " + oldVersion 
							+ " to "
							+ newVersion + ", which will destroy all old data");
			}
            db.execSQL(DATABASE_DROP_1);
			//db.execSQL(DATABASE_DROP_2);
			db.execSQL(DATABASE_DROP_3);
			db.execSQL(DATABASE_DROP_4);
			db.execSQL(DATABASE_DROP_5);
			db.execSQL(DATABASE_DROP_6);
            onCreate(db);
        }
    }    
    
    //---opens the database---
    public DBAdapter open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    //---closes the database---    
    public void close() 
    {
        DBHelper.close();
    }
	
	public void reload()
	{
		DBHelper.onUpgrade(db, 1, 2);
	}
	
	public void deleteAllSettings()
	{
		db.delete("settings", null, null);
	}
	
	public long insertSetting(String name, String value, String note)
	{
		String exists = getSetting(name);
		if (exists != null) {
			return 0;
		}
		
		ContentValues initialValues = new ContentValues();
		initialValues.put("name", name);
		initialValues.put("value", value);
		initialValues.put("note", note);
		return db.insert("settings", null, initialValues);
	}
	
	public Boolean set(String name, String value)
    {
        ContentValues args = new ContentValues();
		args.put("value", value);
		
        return db.update("settings", args, 
                         "name = ?", new String[] {name}) > 0;
	}
	
	public String getSetting(String name)
	{
        Cursor mCursor =
                db.query(true, "settings", new String[] {
                		"value"
                		}, 
                		"name" + "='" + name +"'", 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null && mCursor.moveToFirst()) {
			String ret_val = mCursor.getString(0);
			mCursor.close();
            return ret_val;
        }
		mCursor.close();
        return null;
	}
	
	public Setting[] getSettingsWithValues()
	{
        Cursor c = db.query("settings", new String[] {
        		"name",
				"value",
				"note"
				}, 
                null, 
                null, 
                null, 
                null, 
                null);
		
		int row_count = c.getCount();
		Setting[] settings = new Setting[row_count];
		int counter = 0;
		if (c.moveToFirst())
        {
            do 
			{
				String name = c.getString(0);
				String value = c.getString(1);
				String note = c.getString(2);
				
				if (!"".equals(name))
				{
					settings[counter] = new Setting(name, value, note);
					counter++;
				}
            } 
			while (c.moveToNext());
        }
		c.close();
		return settings;
	}
    
    //---insert a title into the database---
    public long insertContact(String name) 
    {
		long id = getContactByName(name);
		if (id > 0) {
			return 0;
		}
		
        ContentValues initialValues = new ContentValues();
        initialValues.put("name", name);
        return db.insert("contacts", null, initialValues);
    }

    //---retrieves all the titles---
    public String[] getAllContacts() 
    {
        Cursor c = db.query("contacts", new String[] {
        		KEY_ROWID, 
        		"name"}, 
                null, 
                null, 
                null, 
                null, 
                null);
		
		int row_count = c.getCount();
		String[] contacts = new String[row_count];
		int counter = 0;
		if (c.moveToFirst())
        {
            do 
			{
				String name = c.getString(1);
				
				if (!"".equals(name))
				{
					contacts[counter] = name;
					counter++;
				}
            } 
			while (c.moveToNext());
        }
		c.close();
		return contacts;
    }

    //---retrieves a particular title---
    public int getContactCount() throws SQLException 
    {
        Cursor mCursor =
                db.query(true, "contacts", new String[] {
                		"count(*)"
                		}, 
                		null, 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null) {
            mCursor.moveToFirst();
			return mCursor.getInt(0);
        }
        return 0;
    }

    //---retrieves a particular title---
    public Cursor getContact(long rowId) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, "contacts", new String[] {
                		KEY_ROWID,
                		"name"
                		}, 
                		KEY_ROWID + "=" + rowId, 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
	
	public String getContactName(long rowId) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, "contacts", new String[] {
                		"name"
                		}, 
                		KEY_ROWID + "=" + rowId, 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null && mCursor.moveToFirst()) {
			String ret_val = mCursor.getString(0);
			mCursor.close();
            return ret_val;
        }
        else {
			return "";
		}
    }
	
	public long getContactByName(String name)
	{
        Cursor mCursor =
                db.query(true, "contacts", new String[] {
                		KEY_ROWID,
                		"name"
                		}, 
                		"name" + "='" + name + "'", 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null && mCursor.moveToFirst()) {
			Long ret_val = mCursor.getLong(0);
			mCursor.close();
            return ret_val;
        }
        else {
			return 0;
		}
	}

    //---updates a title---
    public boolean updateContact(long rowId, String name) 
    {
        ContentValues args = new ContentValues();
        args.put("name", name);
        return db.update("contacts", args, 
                         KEY_ROWID + "=" + rowId, null) > 0;
    }
	
	public boolean updateContactName(int position,String name,Boolean is_driver,Integer price)
    {
		String[] kontakty = getAllContacts();
		String orig_name = kontakty[position];
		
		long driver = getDriverByName(orig_name);
		if (driver > 0)
		{
			if (is_driver)
			{
				if (ViewConst.DEBUG) {
					Log.i("UPDATE_PROFILE", orig_name+" je řidič a zůstává řidičem");
				}
				if (price != getDriverPrice(driver)) {
					setDriverPrice(driver,price);
				}
			}
			if (!is_driver)
			{
				if (ViewConst.DEBUG) {
					Log.i("UPDATE_PROFILE", "Požadavek na smazání řidiče "+orig_name);
				}
				if (!deleteDriver(orig_name,false).isOK()) {
					return false;
				}
			}
		}
		if (driver <= 0)
		{
			if (is_driver)
			{
				if (ViewConst.DEBUG) {
					Log.i("UPDATE_PROFILE", "Tvořím řidiče "+orig_name);
				}
				insertDriver(orig_name, price);
			}
			if (!is_driver)
			{
				if (ViewConst.DEBUG) {
					Log.i("UPDATE_PROFILE", orig_name+" není řidič a nebude řidičem");
				}
			}
		}
		
		
		if (name.equals(orig_name))
		{
			if (ViewConst.DEBUG) {
				Log.i("UPDATE_PROFILE", "Jméno zůstává nezměněno");
			}
		}
		else
		{
			long id = getContactByName(name);
			long orig = getContactByName(orig_name);
			
			if (id > 0)
			{
				if (ViewConst.DEBUG) {
					Log.i("UPDATE_PROFILE", "Jméno "+name+" již existuje");
				}
				return false;
			}
			else
			{
				if (ViewConst.DEBUG) {
					Log.i("UPDATE_PROFILE", "Jméno "+orig+" změněno na "+name);
				}
				updateContact(orig, name);
			}
		}
		return true;
    }
	/*
	public long getPriceByValue(int value) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, "prizes", new String[] {
                		KEY_ROWID,
                		"price"
                		}, 
                		"price" + "=" + value, 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null && mCursor.moveToFirst()) {
			Long ret_val = mCursor.getLong(0);
			mCursor.close();
            return ret_val;
        }
        else
			return 0;
    }
	
	public int getPriceById(long rowId) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, "prizes", new String[] {
                		KEY_ROWID,
                		"price"
                		}, 
                		KEY_ROWID + "=" + rowId, 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null && mCursor.moveToFirst()) {
			int ret_val = mCursor.getInt(1);
			mCursor.close();
            return ret_val;
        }
        else
			return 0;
    }
	*/
	public long insertPrice(int price)
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put("price", price);
		return db.insert("prizes", null, initialValues);
	}
	
	public long insertDriver(String contact_name, int price)
	{
		long id = getDriverByName(contact_name);
		if (id > 0) {
			return 0;
		}
		
		long contact_id = getContactByName(contact_name);
		
		if (contact_id <= 0)
		{
			contact_id = insertContact(contact_name);
		}
		
		ContentValues initialValues = new ContentValues();
		initialValues.put("contact_id", contact_id);
		initialValues.put("price", price);
		return db.insert("drivers", null, initialValues);
	}
	
	public boolean setDriverPrice(long driver, int price)
	{
		ContentValues args = new ContentValues();
        args.put("price", price);
        return db.update("drivers", args, 
                         KEY_ROWID + "=" + driver, null) > 0;
	}
	
	/**
	 * Delete driver even if way count > 0
	 * 
	 * @param name
	 * @param delete_contact
	 * @return 
	 */
	public BooleanMesssage deleteDriver(String name, Boolean delete_contact)
	{
		return deleteDriver(name, delete_contact, true);
	}
	
	public BooleanMesssage deleteDriver(String name, Boolean delete_contact, Boolean delete_with_ways)
	{
		BooleanMesssage ret = new BooleanMesssage(true,"");
		// kontrola poctu jizd
		int way_count_d = getWayCountByDriverName(name, "");
		int way_count_c = getWayCountByContactName(name, "");
		
		// kontrola vzajemne financni bilance
		BooleanMesssage can_delete = maNulovouFinancniBilanci(name);
		
		if (ViewConst.DEBUG) {
			Log.i("DELETE_DRIVER_CONTACT",name+" odřídil "+way_count_d+" a jako pasažér jel "+way_count_c+" krát.");
		}
		if (can_delete.isOK())
		{
			if (ViewConst.DEBUG) {
				Log.i("DELETE_DRIVER_CONTACT","Nulová bilance");
			}
			ret.addMsg("Smazán kontakt "+name);
		}
		else 
		{
			if (ViewConst.DEBUG) {
				Log.i("DELETE_DRIVER_CONTACT",can_delete.getMsg());
			}
			ret.setFalse();
			ret.addMsg("Nelze smazat!");
			ret.addMsg(can_delete.getMsg());
		}
		
		ret.addMsg("Řidič: "+way_count_d+" krát");
		ret.addMsg("Pasažér "+(way_count_c-way_count_d)+" krát");
		
		if (!delete_with_ways && way_count_d > 0)
		{
			ret.setFalse();
			if (ViewConst.DEBUG) {
				Log.i("DELETE_DRIVER_CONTACT","Řidič "+name+" nemůže být smazán ("+way_count_d+" jízd)");
			}
		}
		
		if (!delete_with_ways && delete_contact && way_count_c > 0)
		{
			ret.setFalse();
			if (ViewConst.DEBUG) {
				Log.i("DELETE_DRIVER_CONTACT","Kontakt "+name+" nemůže být smazán ("+way_count_c+" jízd)");
			}
		}
		
		// zpracovani operace mazani
		if (ret.isOK())
		{
			long driver = getDriverByName(name);
			if (driver > 0)
			{
				// smazat ridice
				db.delete("drivers", KEY_ROWID+"="+driver, null);
			}
			if (delete_contact) {
				// smazat vsechny jizdy
				removeContactDriveWays(name);
				// smazat vsechny platby
				removeContactPayments(name);
				// smazat kontakt
				db.delete("contacts", "name='"+name+"'", null);
			}
		}
		
		return ret;
	}
	
	public long getDriverByName(String name)
	{
		long contact = getContactByName(name);
        Cursor mCursor =
                db.query(true, "drivers", new String[] {
                		KEY_ROWID
                		}, 
                		"contact_id" + "=" + contact, 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null && mCursor.moveToFirst()) {
			Long ret_val = mCursor.getLong(0);
			mCursor.close();
            return ret_val;
        }
        else {
			return 0;
		}
	}
	
	public String getDriverName(long rowId) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, "drivers", new String[] {
                		"contact_id"
                		}, 
                		KEY_ROWID + "=" + rowId, 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null && mCursor.moveToFirst()) {
            Long contact = mCursor.getLong(0);
			mCursor.close();
			return getContactName(contact);
        }
        else {
			return "";
		}
    }
	
	public String[] getAllDrivers()
    {
        Cursor c = db.query("drivers", new String[] {
        		KEY_ROWID, 
        		"contact_id"}, 
                null, 
                null, 
                null, 
                null, 
                null);
		
		int row_count = c.getCount();
		String[] drivers = new String[row_count];
		int counter = 0;
		if (c.moveToFirst())
        {
            do {          
                long contact_id = c.getLong(1);
				String name = getContactName(contact_id);
				
				if (!"".equals(name))
				{
					drivers[counter] = name;
					counter++;
				}
            } 
			while (c.moveToNext());
        }
		c.close();
		return drivers;
    }
	
	public Cursor getWaysWithDriver(long contact, long driver, String date_prefix)
    {
		if (ViewConst.DEBUG) {
			Log.i("GET_WAYS_WITH_DRIVER","Kontakt ID: "+contact+", Řidič ID: "+driver);
		}
		
		String where = "date" + " LIKE '" + date_prefix + "%'";
		where += " AND passenger_id" + "=" + contact;
		where += " AND driver_id" + "=" + driver;
		
		if (ViewConst.DEBUG) {
			Log.i("GET_WAYS_WITH_DRIVER","Podmínka: "+where);
		}
		
        return db.query("driveways", new String[] {
        		KEY_ROWID, 
        		"date",
				"direction",
				"price"
				}, 
                where,
                null, 
                null, 
                null, 
                null);
    }
	
	public int getWayCountByDriverName(String driver_name, String date_prefix)
    {
		long driver = getDriverByName(driver_name);
		if (ViewConst.DEBUG) {
			Log.i("GET_WAY_COUNT_BY_DRIVER_NAME","Řidič: "+driver_name+" ID: "+driver);
		}
		
		String where = "date" + " LIKE '" + date_prefix + "%'"
					 + " AND "
					 +" driver_id" + "=" + driver;
		
		if (ViewConst.DEBUG) {
			Log.i("GET_WAY_COUNT_BY_DRIVER_NAME","Podmínka: "+where);
		}
		
        Cursor ways = db.query("driveways", new String[] {
        		KEY_ROWID, 
        		"date",
				"direction",
				"price"
				}, 
                where,
                null, 
                "date,direction", 
                null, 
                null);
		
		int count = 0;
		if (ways != null && ways.moveToFirst())
		{
            do {
				if (ViewConst.DEBUG) {
					Log.i("GET_WAY_COUNT_BY_DRIVER_NAME","Datum "+ways.getString(1)+", směr: "+ways.getString(2)+", cena: "+ways.getInt(3));
				}
                count ++;
            } 
			while (ways.moveToNext());
		}
		ways.close();
		
		return count;
    }
	
	public int getWayCountByContactName(String contact_name, String date_prefix)
    {
		long contact = getContactByName(contact_name);
		if (ViewConst.DEBUG) {
			Log.i("GET_WAY_COUNT_BY_CONTACT_NAME","Pasažér: "+contact_name+" ID: "+contact);
		}
		
		String where = "date" + " LIKE '" + date_prefix + "%'"
					 + " AND "
					 +" passenger_id" + "=" + contact;
		
		if (ViewConst.DEBUG) {
			Log.i("GET_WAY_COUNT_BY_CONTACT_NAME","Podmínka: "+where);
		}
		
        Cursor ways = db.query("driveways", new String[] {
        		KEY_ROWID, 
        		"date",
				"direction",
				"price"
				}, 
                where,
                null, 
                null, 
                null, 
                null);
		
		int count = 0;
		if (ways != null && ways.moveToFirst())
		{
            do {
				if (ViewConst.DEBUG) {
					Log.i("GET_WAY_COUNT_BY_CONTACT_NAME","Datum "+ways.getString(1)+", směr: "+ways.getString(2)+", cena: "+ways.getInt(3));
				}
                count ++;
            } 
			while (ways.moveToNext());
		}
		ways.close();
		
		return count;
    }
	
	public Cursor getWayOnDate(String datum, String smer)
    {
        return db.query("driveways", new String[] {
        		KEY_ROWID,
				"driver_id",
				"passenger_id",
				"price"
				}, 
                "date" + "='" + datum +"'"
				+" AND "+
				"direction" + "='" + smer + "'",
                null, 
                null, 
                null, 
                null);
    }
	
	public ArrayList<String> getPassengersOnWay(String datum, String smer, long driver)
	{
		Cursor c = db.query("driveways", new String[] {
				"passenger_id"
				}, 
                "date" + "='" + datum +"'"
				+" AND "+
				"direction" + "='" + smer + "'"
				+" AND "+
				"driver_id" + "=" + driver,
                null, 
                null, 
                null, 
                null);	
	
		if (c != null && c.moveToFirst())
		{
			ArrayList<String> list = new ArrayList<String>();
			do 
			{
				String name = getContactName(c.getLong(0));
				list.add(name);
			}
			while (c.moveToNext());
			
			if (ViewConst.DEBUG) {
				Log.i("GET_PASSENGERS_ON_WAY", "Nalezeno "+list.size()+" pasažérů");
			}
			
			c.close();
			return list;
		}
		else {
			return null;
		}
	}
	
	public ArrayList<Driveway> getAllWays(String driver_name, String passenger_name, String date_prefix)
    {
		// nastaveni prefixu
		String where = "date LIKE '"+date_prefix+"%'";
		
		if (ViewConst.DEBUG) {
			Log.i("GET_ALL_WAYS", where);
		}
		
		Long driver_id = getDriverByName(driver_name);
		Long passenger_id = getContactByName(passenger_name);
		if (driver_id > 0) {
			where += " AND driver_id = "+driver_id;
		}
		if (passenger_id > 0) {
			where += " AND passenger_id = "+passenger_id;
		}
		
        Cursor c = db.query(true, "driveways", new String[] {
				"date",
				"direction",
				"driver_id"
				}, 
				where,
				null, null, null, 
				"date"+" DESC" , 
				null);
		
		if (c != null && c.moveToFirst())
		{
			ArrayList<Driveway> list = new ArrayList<Driveway>();
			
			do
			{
				String datum = c.getString(0);
				String smer = c.getString(1);
				Long driver = c.getLong(2);
				
				String ridic = getDriverName(driver);
				
				Driveway way = new Driveway(datum, smer, ridic);
				
				// najit pasazery v teto kombinaci
				ArrayList<String> pasazeri = getPassengersOnWay(datum, smer, driver);
				for (int i=0; i<pasazeri.size(); i++)
				{
					// ovlivni pocet pasazeru, napr. pri poctu 3 se bude zobrazovat 5
					if (way.pasazeri.length > 0) {
						way.addPasazer(", ");
					}
					way.addPasazer(pasazeri.get(i));
					if (ViewConst.DEBUG) {
						Log.i("GET ALL WAYS","Ukládám pasažéra "+pasazeri.get(i));
					}
				}
				
				list.add(way);
			}
			while (c.moveToNext());
			
			c.close();
			return list;
		}
		else {
			return null;
		}
    }
	
	public long insertPayment(String datum, String receiving_name, String paying_name, int amount, String note)
	{
        ContentValues initialValues = new ContentValues();
        
		long receiving = getContactByName(receiving_name);
		long paying = getContactByName(paying_name);
		
		if (receiving <= 0 || paying <= 0) {
			return 0;
		}
		
		initialValues.put("date", datum);
		initialValues.put("receiving_id", receiving);
		initialValues.put("paying_id", paying);
		initialValues.put("amount", amount);
		initialValues.put("note", note);
        return db.insert("payments", null, initialValues);
	}
	
	public ArrayList<Payment> getAllPayments(String driver_name, String passenger_name)
    {
        String where = KEY_ROWID+">0";
		Long driver_id = getContactByName(driver_name);
		Long passenger_id = getContactByName(passenger_name);
		if (driver_id > 0) {
			where += " AND receiving_id = "+driver_id;
		}
		if (passenger_id > 0) {
			where += " AND paying_id = "+passenger_id;
		}
		
        Cursor c = db.query("payments", new String[] {
        		KEY_ROWID, 
        		"date",
				"amount",
				"receiving_id",
				"paying_id",
				"note"
				}, 
                where,
                null, 
                null, 
                null,
				"date"+" DESC" );
		
		if (c != null && c.moveToFirst())
		{
			ArrayList<Payment> list = new ArrayList<Payment>();
			
			do
			{
				String kdo = getContactName(c.getLong(4));
				String komu = getContactName(c.getLong(3));
				Payment p = new Payment(c.getString(1), kdo, komu, ""+c.getLong(2), c.getString(5));
				
				list.add(p);
			}
			while (c.moveToNext());
			
			c.close();
			return list;
		}
		else {
			return null;
		}
    }
	
	public Cursor getPaymentsBeetweenContacts(long paying, long receiving)
    {
        return db.query("payments", new String[] {
        		KEY_ROWID, 
        		"date",
				"amount",
				"note"
				}, 
                "paying_id" + "=" + paying
				+" AND "+
				"receiving_id" + "=" + receiving,
                null, 
                null, 
                null, 
                null);
    }
	
	public int getWayCountWithDriver(String contact_name,String driver_name, String date_prefix)
	{
		if (ViewConst.DEBUG) {
			Log.i("GET_WAY_COUNT_WITH_DRIVER","Kontakt "+contact_name+", řidič: "+driver_name);
		}
		
		long driver = getDriverByName(driver_name);
		long contact = getContactByName(contact_name);
		Cursor ways = getWaysWithDriver(contact, driver, date_prefix);
		
		int count = 0;
		if (ways != null && ways.moveToFirst())
		{
            do {
				if (ViewConst.DEBUG) {
					Log.i("GET_WAY_COUNT_WITH_DRIVER","Datum "+ways.getString(1)+", směr: "+ways.getString(2)+", cena: "+ways.getInt(3));
				}
                count ++;
            } 
			while (ways.moveToNext());
		}
		ways.close();
		
		if (ViewConst.DEBUG) {
			Log.i("GET_WAY_COUNT_WITH_DRIVER","Počet jízd: "+count);
		}
		
		return count;
	}
	
	public int getAmountToPayWithDriver(String contact_name,String driver_name)
	{
		long contact = getContactByName(contact_name);
		long driver = getContactByName(driver_name);
		
		// zjistit castku za jizdy
		
		int amount = getAmountWithDriver(contact, driver);
		if (ViewConst.DEBUG) {
			Log.i("GET AMOUNT TO PAY", contact_name+" má zaplatit řidiči "+driver_name+" částku "+amount);
		}
		
		// odecist platby
		int paid = getPaidAmountBeetweenContacts(contact, driver);
		if (ViewConst.DEBUG) {
			Log.i("GET AMOUNT TO PAY", contact_name+" už zaplatil řidiči "+driver_name+" částku "+paid);
		}
		// platby obracene
		int paid_two = getPaidAmountBeetweenContacts(driver, contact);
		
		// odecist castku za jizdy, pokud jsou oba ridici
		int amount_two = 0;
		long driver_two = getDriverByName(contact_name);
		if (driver_two > 0)
		{
			long driver_contact = getContactByName(driver_name);
			amount_two = getAmountWithDriver(driver_contact, driver_two);
		}
		if (ViewConst.DEBUG) {
			Log.i("GET AMOUNT TO PAY", driver_name+" projezdil s řidičem "+contact_name+" částku "+amount_two);
		}
		if (ViewConst.DEBUG) {
			Log.i("GET AMOUNT TO PAY", driver_name+" už zaplatil řidiči "+contact_name+" částku "+paid_two);
		}
		
		int to_pay = amount + paid_two - paid - amount_two;
		if (ViewConst.DEBUG) {
			Log.i("GET AMOUNT TO PAY", contact_name+" dluží řidiči "+driver_name+" částku "+to_pay);
		}
		
		return to_pay;
	}
	
	public int getAmountWithDriver(long contact, long driver)
	{
		Cursor ways = getWaysWithDriver(contact,driver,"");
		int amount = 0;
		if (ways != null && ways.moveToFirst())
		{
            do {          
                amount += ways.getInt(3);
            } 
			while (ways.moveToNext());
		}
		ways.close();
		return amount;
	}
	
	public int getPaidAmountBeetweenContacts(long paying, long receiving)
	{
		Cursor payments = getPaymentsBeetweenContacts(paying, receiving);
		int paid = 0;
		if (payments != null && payments.moveToFirst())
		{
            do {          
                paid += payments.getInt(2);
            } 
			while (payments.moveToNext());
		}
		payments.close();
		return paid;
	}
	
	public String[] getPassengersOnWay(Cursor way)
	{
		if (way == null || !way.moveToFirst()) {
			return null;
		}
		
		int count = way.getCount();
		String[] passengers = new String[count];
		int i = 0;
		do {
			long contact = way.getLong(2);
			String name = getContactName(contact);
			if (ViewConst.DEBUG) {
				Log.i("GET PASSENGERS","Pasažér "+(i+1)+": "+name);
			}
			passengers[i] = name;
			i++;
		}
		while (way.moveToNext());
		
		return passengers;
	}
	
	public String getDriverOnWay(Cursor way)
	{
		if (way == null || !way.moveToFirst()) {
			return null;
		}
		
		long driver = way.getLong(1);
		
		return getDriverName(driver);
	}
	
	public Boolean canSaveWayOnDate(String datum, String smer, long contact)
	{
        Cursor c = db.query("driveways", new String[] {
        		KEY_ROWID
				}, 
                "passenger_id" + "=" + contact
				+" AND "+
				"date" + "='" + datum + "'"
				+" AND "+
				"direction" + "='" + smer + "'"
				,
                null, 
                null, 
                null, 
                null);
		
		if (c != null && c.moveToFirst())
		{
			c.close();
			return false;
		}
		else
		{
			c.close();
			return true;
		}
	}
	
	public Boolean canSaveWayOnDate(String datum, String smer)
	{
        Cursor c = db.query("driveways", new String[] {
        		KEY_ROWID
				},
				"date" + "='" + datum + "'"
				+" AND "+
				"direction" + "='" + smer + "'"
				,
                null, 
                null, 
                null, 
                null);
		
		if (c != null && c.moveToFirst())
		{
			c.close();
			return false;
		}
		else
		{
			c.close();
			return true;
		}
	}
	
	public void removeDriveWay(String datum, String smer)
	{
		db.delete("driveways", "date = ? AND direction = ?", new String[] {datum, smer});
	}
	
	public void removeContactDriveWays(String name)
	{
		long contact = getContactByName(name);
		db.delete("driveways", "driver_id = "+contact+" OR passenger_id = "+contact, null);
	}
	
	public void removeContactPayments(String name)
	{
		long contact = getContactByName(name);
		db.delete("payments", "receiving_id = "+contact+" OR paying_id = "+contact, null);
	}
	
	// pri ukladani kontrolovat unikatnost DATE a DIRECTION
	public Boolean saveDriveWay(String ridic, String datum, String smer, int passenger_count, String[] passengers)
	{
		if (!canSaveWayOnDate(datum, smer))
		{
			if (!"true".equals(getSetting("can_modify_way"))) {
				return false;
			}
			else
			{
				removeDriveWay(datum, smer);
			}
		}
		
		long driver = getDriverByName(ridic);
		int price = getDriverPrice(driver);
		
		for (int i=0; i<passenger_count; i++)
		{
			long contact = getContactByName(passengers[i]);
			
			if (canSaveWayOnDate(datum, smer, contact))
			{
				// ulozit radek
				long id = insertNewWayOnDate(datum, smer, driver, contact, price);
				if (ViewConst.DEBUG) {
					Log.i("INSERT DRIVE WAY", "Datum: "+datum+", Smer: "+smer+", Ridic: "+ridic+"("+driver+"), Pasazer: "+passengers[i]+"("+contact+"), Cena: "+price+" - uloženo ID "+id);
				}
			}
		}
		long driver_contact = getContactByName(ridic);

		if (canSaveWayOnDate(datum, smer, driver_contact))
		{
			// ulozit radek
			long id = insertNewWayOnDate(datum, smer, driver, driver_contact, price);
			if (ViewConst.DEBUG) {
				Log.i("INSERT DRIVE WAY - driver", "Datum: "+datum+", Smer: "+smer+", Ridic: "+ridic+"("+driver+"), Pasazer: "+ridic+"("+driver_contact+")"+", Cena: "+price+" - uloženo ID "+id);
			}
		}
		
		return true;
	}
	
	public int getDriverPrice(long driver)
	{
        Cursor c = db.query("drivers", new String[] {
        		KEY_ROWID,
				"price"
				}, 
                KEY_ROWID + "=" + driver,
                null, 
                null, 
                null, 
                null);
		
		if (c != null && c.moveToFirst())
		{
			int ret_val = c.getInt(1);
			c.close();
			return ret_val;
		}
		else {
			return 0;
		}
	}    
	
	public long insertNewWayOnDate(String datum, String smer, long driver, long passenger, int price) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put("date", datum);
		initialValues.put("direction", smer);
		initialValues.put("driver_id", driver);
		initialValues.put("passenger_id", passenger);
		initialValues.put("price", price);
		
        return db.insert("driveways", null, initialValues);
    }

	private BooleanMesssage maNulovouFinancniBilanci(String name) 
	{
		BooleanMesssage ret = new BooleanMesssage(true, "Finanční bilance:");
		if (ViewConst.DEBUG) {
			Log.i("NULOVA FINANCNI BILANCE?", "Kontakt: "+name);
		}
		
		String drivers[] = getAllDrivers();
		for (int i=0;i<drivers.length;i++)
		{
			int dluh = getAmountToPayWithDriver(name, drivers[i]);
			if (ViewConst.DEBUG) {
				Log.i("NULOVA FINANCNI BILANCE?", "Bilance s řidičem "+drivers[i]+": "+dluh);
			}
			ret.addMsg(drivers[i]+": "+dluh+",- Kč");
			if (dluh != 0)
			{
				ret.setFalse();
			}
		}
		
		return ret;
	}
}
